﻿using System;
using System.Collections.Generic;
using System.Text;
using Net4.Common.Services;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using Net4.Common.Entities;
using Net4.Common.Data;

namespace Net4.Services.Access
{
    public class AccessFileService
    {
        private static AccessFileService _Instance = new AccessFileService();

        public static AccessFileService Instance
        {
            get { return AccessFileService._Instance; }
        }

        #region AccessFileService Members

        private static readonly string INSERT_ATTACHMENT =
@"INSERT INTO [Attachments]([ParentId], [FileName], [Extension], [Size], [Width], [Height]) 
    VALUES(@ParentId, @FileName, @Extension, @Size, @Width, @Height)";
        /// <summary>
        /// Inserts the specified Attachment.
        /// </summary>
        /// <param name="article">The Attachment.</param>
        public void Insert(Attachment attachment)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(INSERT_ATTACHMENT);
            db.AddInParameter(cmd, "@ParentId", DbType.Int32, attachment.ParentId);
            db.AddInParameter(cmd, "@FileName", DbType.String, attachment.FileName);
            db.AddInParameter(cmd, "@Extension", DbType.String, attachment.Extension);
            db.AddInParameter(cmd, "@Size", DbType.Int32, attachment.Size);
            if (attachment is ImageInfo)
            {
                ImageInfo image = attachment as ImageInfo;
                db.AddInParameter(cmd, "@Width", DbType.Int32, image.Width);
                db.AddInParameter(cmd, "@Height", DbType.Int32, image.Height);
            }
            else
            {
                db.AddInParameter(cmd, "@Width", DbType.Int32, 0);
                db.AddInParameter(cmd, "@Height", DbType.Int32, 0);
            }
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();
                try
                {
                    db.ExecuteNonQuery(cmd, transaction);

                    cmd.Parameters.Clear();
                    cmd.CommandText = Utilis.GET_LAST_INSERT_IDENTITY;
                    attachment.Id = (int)db.ExecuteScalar(cmd, transaction);

                    transaction.Commit();
                }
                catch(Exception e)
                {
                    // Roll back the transaction. 
                    transaction.Rollback();
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        private static readonly string UPDATE_ATTACHMENT =
@"UPDATE [Attachments] SET [FileName]=@FileName, [Extension]=@Extension, 
    [Size]=@Size, [Width]=@Width, [Height]=@Height WHERE [Id]=@Id";
        /// <summary>
        /// Updates the specified Attachment.
        /// </summary>
        /// <param name="article">The Attachment.</param>
        public void Update(Attachment attachment)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(UPDATE_ATTACHMENT);
            db.AddInParameter(cmd, "@ParentId", DbType.Int32, attachment.ParentId);
            db.AddInParameter(cmd, "@FileName", DbType.String, attachment.FileName);
            db.AddInParameter(cmd, "@Extension", DbType.String, attachment.Extension);
            db.AddInParameter(cmd, "@Size", DbType.Int32, attachment.Size);
            if (attachment is ImageInfo)
            {
                ImageInfo image = attachment as ImageInfo;
                db.AddInParameter(cmd, "@Width", DbType.Int32, image.Width);
                db.AddInParameter(cmd, "@Height", DbType.Int32, image.Height);
            }
            else
            {
                db.AddInParameter(cmd, "@Width", DbType.Int32, 0);
                db.AddInParameter(cmd, "@Height", DbType.Int32, 0);
            }
            db.AddInParameter(cmd, "@Id", DbType.Int32, attachment.Id);

            db.ExecuteNonQuery(cmd);
        }

        private static readonly string DELETE_ATTACHMENT =
@"DELETE FROM [Attachments] WHERE [Id]=@Id";
        /// <summary>
        /// Deletes the specified attachment id.
        /// </summary>
        /// <param name="attachmentId">The attachment id.</param>
        public void Delete(int attachmentId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(DELETE_ATTACHMENT);
            db.AddInParameter(cmd, "@Id", DbType.Int32, attachmentId);

            db.ExecuteNonQuery(cmd);
        }

        private static readonly string SELECT_ATTACHMENT =
@"SELECT * FROM [Attachments] WHERE [Id]=@Id";
        /// <summary>
        /// Selects the specified attachment id.
        /// </summary>
        /// <param name="attachmentId">The attachment id.</param>
        /// <returns></returns>
        public Attachment Select(int attachmentId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(SELECT_ATTACHMENT);
            db.AddInParameter(cmd, "@Id", DbType.Int32, attachmentId);
            using (SafeDataReader reader = new SafeDataReader(db.ExecuteReader(cmd)))
            {
                if (reader.Read())
                {
                    Attachment attach;
                    if (AttachmentManager.Instance.IsImage(reader.GetString("Extension")))
                    {
                        ImageInfo image = new ImageInfo();
                        image.Width = reader.GetInt32("Width");
                        image.Height = reader.GetInt32("Height");
                        attach = image;
                    }
                    else
                    {
                        attach = new Attachment();
                    }
                    attach.Id = reader.GetInt32("Id");
                    attach.FileName = reader.GetString("FileName");
                    attach.ParentId = reader.GetInt32("ParentId");
                    attach.Extension = reader.GetString("Extension");
                    attach.Size = reader.GetInt32("Size");

                    return attach;
                }
                return null;
            }
        }

        private static readonly string SELECT_ATTACHMENTS =
@"SELECT * FROM [Attachments] WHERE [ParentId]=@ParentId ORDER BY [Id]";
        /// <summary>
        /// Selects the attachments.
        /// </summary>
        /// <param name="parentId">The parent id.</param>
        /// <returns></returns>
        public List<Attachment> SelectAttachments(int parentId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(SELECT_ATTACHMENTS);
            db.AddInParameter(cmd, "@ParentId", DbType.Int32, parentId);
            
            using (SafeDataReader reader = new SafeDataReader(db.ExecuteReader(cmd)))
            {
                List<Attachment> list = new List<Attachment>();
                while (reader.Read())
                {
                    Attachment attach;
                    if (AttachmentManager.Instance.IsImage(reader.GetString("Extension")))
                    {
                        ImageInfo image = new ImageInfo();
                        image.Width = reader.GetInt32("Width");
                        image.Height = reader.GetInt32("Height");
                        attach = image;
                    }
                    else
                    {
                        attach = new Attachment();
                    }
                    attach.Id = reader.GetInt32("Id");
                    attach.FileName = reader.GetString("FileName");
                    attach.ParentId = reader.GetInt32("ParentId");
                    attach.Extension = reader.GetString("Extension");
                    attach.Size = reader.GetInt32("Size");

                    list.Add(attach);
                }
                return list;
            }
        }

        private static readonly string GET_ATTACHMENT_TOTALCOUNT = @"SELECT COUNT([Id]) FROM [Attachments]";
        /// <summary>
        /// Gets the total count.
        /// </summary>
        /// <returns></returns>
        public int GetTotalCount()
        { 
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(GET_ATTACHMENT_TOTALCOUNT);
            return (int)db.ExecuteScalar(cmd);
        }

        #endregion
    }
}
